MySQL Procedural SQL Interview Guide

Introduction to Procedural SQL in MySQL

MySQL's Procedural SQL (also known as MySQL stored programs) allows you to write logic-driven, reusable code inside the database itself. Unlike basic SQL (which is declarative), procedural SQL introduces:

Variables
Loops
Conditions
Exception handling
Reusable blocks (procedures/functions)

This makes database-side programming more powerful and modular.

1. Variables in MySQL

📘 Theory:

Variables are used to store values temporarily in memory. You must declare them using DECLARE and assign using SET.

📌 Key Points:

Scope is within the block they are declared.
You can define data types like INT, VARCHAR, DECIMAL, etc.

✅ Example:

DECLARE v_salary INT;
SET v_salary = 45000;

2. Control Structures

📘 Theory:

Control structures allow conditional and repetitive execution of code. MySQL supports:

IF...THEN...ELSE
CASE
LOOP
WHILE
REPEAT

🔸 IF...THEN...ELSE

IF condition THEN
  -- action
ELSE
  -- alternative action
END IF;

Used for branching based on a condition.

🔸 CASE Statement

CASE grade
  WHEN 'A' THEN SET remark = 'Excellent';
  WHEN 'B' THEN SET remark = 'Good';
  ELSE SET remark = 'Poor';
END CASE;

Used for multi-condition branching (like switch-case).

🔸 LOOP

label: LOOP
  -- statements
  LEAVE label; -- to exit the loop
END LOOP;

A basic loop that must be manually exited using LEAVE.

🔸 WHILE

WHILE condition DO
  -- repeat
END WHILE;

Runs as long as condition is TRUE.

🔸 REPEAT

REPEAT
  -- code
UNTIL condition
END REPEAT;

Runs the block at least once, then checks condition.

3. Stored Procedures

📘 Theory:

A stored procedure is a named set of SQL statements saved in the database. It performs an action but does not return a value directly (though OUT parameters can be used).

📌 Key Benefits:

Reusability
Modularity
Improved performance (stored once, parsed once)

✅ Syntax:

CREATE PROCEDURE proc_name (IN param1 INT)
BEGIN
  -- statements
END;

4. Stored Functions

📘 Theory:

A stored function is like a procedure but it returns a single value directly using RETURN.

📌 Use Cases:

Compute totals, averages, or results used in SELECT queries.

✅ Syntax:

CREATE FUNCTION func_name(param INT) RETURNS INT
BEGIN
  RETURN param * 2;
END;

You can now use:

SELECT func_name(10);  -- returns 20

5. Triggers

📘 Theory:

A trigger is a block of code that automatically runs in response to events like INSERT, UPDATE, or DELETE.

📌 Trigger Timing:

BEFORE INSERT / AFTER INSERT
BEFORE UPDATE / AFTER UPDATE
BEFORE DELETE / AFTER DELETE

📌 Use Cases:

Auto-fill columns (e.g. timestamps)
Enforce business rules
Audit logs

✅ Syntax:

CREATE TRIGGER before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;

6. Cursors

📘 Theory:

A cursor is a pointer that allows row-by-row processing of query results.

📌 When to Use:

When you need to process each row individually
Alternatives like JOINs or batch updates are better for performance

✅ Key Steps:

DECLARE cursor
OPEN cursor
FETCH from cursor
CLOSE cursor

✅ Example:

DECLARE done INT DEFAULT 0;
DECLARE emp_name VARCHAR(50);
DECLARE cur CURSOR FOR SELECT name FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
  FETCH cur INTO emp_name;
  IF done THEN LEAVE read_loop; END IF;
  -- Process each employee
END LOOP;
CLOSE cur;

7. Exception Handling (HANDLERS)

📘 Theory:

MySQL lets you handle exceptions using DECLARE HANDLER statements.

📌 Types:

CONTINUE – continue execution after error
EXIT – exit the block immediately

📌 Common Conditions:

SQLEXCEPTION – general SQL error
NOT FOUND – no more rows in cursor
SQLWARNING – warnings

✅ Example:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  SET @error_message = 'Something went wrong';

8. Parameters (IN, OUT, INOUT)

📘 Theory:

Procedures can accept and return parameters:

IN: Input to procedure
OUT: Output from procedure
INOUT: Both input and output

✅ Example:

CREATE PROCEDURE add_numbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

To call:

CALL add_numbers(5, 3, @result);
SELECT @result;

9. Events (Scheduled Jobs)

📘 Theory:

An event is a scheduled task run at a specific time or interval.

📌 Use Cases:

Clean up old logs
Run batch jobs
Send reminders

✅ Example:

CREATE EVENT delete_old_logs
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

Don't forget: SET GLOBAL event_scheduler = ON;

10. DELIMITER

📘 Theory:

The DELIMITER command is not SQL, but a MySQL client instruction used to change the end-of-statement marker so you can define complex blocks (which may include ; inside them).

✅ Usage:

DELIMITER //
CREATE PROCEDURE my_proc() BEGIN
  SELECT 'Hello';
END;
//
DELIMITER ;

11. Difference Between Procedure vs Function

Feature	Procedure	Function
Return Value	No (uses OUT)	Yes (RETURN keyword)
Use in SELECT	No	Yes
Purpose	Perform task	Compute and return a value
Call syntax	CALL proc()	SELECT func()

🧠 Final Tips for Interview

Learn how to use parameters and cursors inside procedures.
Understand trigger types and timing.
Practice how exception handling works with HANDLER.
Know the differences between stored procedure, function, trigger.
Be ready to write basic procedure/function on the spot.